﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;

using SD.LLBLGen.Pro.QuerySpec;
using SD.LLBLGen.Pro.QuerySpec.Adapter;
using SD.LLBLGen.Pro.ORMSupportClasses;

using VNet.VisiSuite.Common.Logger;
using VNet.VisiSuite.Dal.DatabaseSpecific;
using VNet.VisiSuite.Dal.EntityClasses;
using VNet.VisiSuite.Dal.FactoryClasses;
using VNet.VisiSuite.Dal.HelperClasses;


namespace VNet.VisiSuite.ProfitFabImport
{
    public class ProfitFabJob
    {

        public int ProductionPlanExternalId { get; set; }
        public string JobId { get; set; }
        public int DronId { get; set; }
        public int Pass { get; set; }
        public int StationNum { get; set; }
        public DateTime Start { get; set; }
        public int SortOrder { get; set; }

        public int ProductExternalId { get; set; }
        public string Sku { get; set; }
        public string PartRevision { get; set; }
        public string PartDescription { get; set; }
        public int Quantity { get; set; }

        public string Status { get; set; }
        public decimal Rate { get; set; }
        public float SetupTime { get; set; }
        public float PartTime { get; set; }
        public float CycleTime { get; set; }
        public float LoadUnloadTime { get; set; }

        public int CellId { get; set; }
        public int? ProductId { get; set; }
        public int? ProductionPlanId { get; set; }
        public int? OperationId { get; set; }

        public ProductionPlanEntity ProductionPlan { get; set; }

        // public string Foo { get; set; }

        public string WorkOrder
        {
            get
            {
                string work_order = JobId; // +"-" + DronId.ToString() + "-" + Pass.ToString();
                return work_order;
            }
        }
        public string ProductKey { get { return Sku + "-" + PartRevision; } }

    }

    class ProfitFab
    {
        static private readonly int _area_id = 13;

        static private void truncateVisiSuiteTable(DataAccessAdapter adapter, string table_name)
        {
            var qry = new ActionQuery(new SqlCommand("TRUNCATE TABLE VisiSuite.dbo." + table_name));
            int ret = adapter.ExecuteActionQuery(qry);
        }

        static private List<ProfitFabJob> getScheduledJobs()
        {
            List<ProfitFabJob> jobs = null;
            var now = DateTime.Now;
            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var qf = new QueryFactory();
                    var query = qf.Create()
                        .Select(() => new ProfitFabJob
                        {
                            ProductionPlanExternalId = SchdShipFields.SchNum.ToValue<int>(),
                            JobId = SchdShipFields.JobNo.ToValue<string>(),
                            Start = now, // SchdOperFields.StartDate.ToValue<DateTime>(),
                            Pass = OschdFields.Pass.ToValue<int>(),
                            SortOrder = OschdFields.SrtNum.ToValue<int>(),
                            StationNum = OschdFields.StnNum.ToValue<int>(),

                            DronId = StationFields.DroNum.ToValue<int>(),

                            ProductExternalId = SchdShipFields.PartNum.ToValue<int>(),
                            Sku = SchdShipFields.PartNo.ToValue<string>(),
                            PartRevision = SchdShipFields.Rev.ToValue<string>(),
                            PartDescription = SchdShipFields.Description.ToValue<string>(),
                            Quantity = SchdShipFields.ActProdQty.ToValue<int>(),

                            Rate = SrouteFields.Rate.ToValue<decimal>(),
                            Status = SrouteFields.Status.ToValue<string>(),
                            SetupTime = SrouteFields.SetupTime.ToValue<float>(),
                            PartTime = SrouteFields.PartTime.ToValue<float>(),
                            CycleTime = SrouteFields.Pptime1.ToValue<float>(),
                            LoadUnloadTime = SrouteFields.Pptime2.ToValue<float>(),

                            CellId = CellFields.CellId.ToValue<int>(),
                            ProductId = ProductFields.ProductId.ToValue<int?>(),
                            ProductionPlanId = ProductionPlanFields.ProductionPlanId.ToValue<int?>(),
                            OperationId = OperationFields.OperationId.ToValue<int?>(),

                            // Foo = StringFunctions.Concat(StringFunctions.Concat(StringFunctions.Concat(StringFunctions.Concat(SchdOperFields.JobNo, "-"), ConvertFunctions.ToString(StationFields.DroNum)), "-"), ConvertFunctions.ToString(SchdOperFields.Pass)).ToValue<string>()

                        })
                        .From(qf.Oschd
                            .InnerJoin(qf.Station).On(OschdFields.StnNum == StationFields.StnNum)
                            .InnerJoin(qf.SchdShip).On(SchdShipFields.SchNum == OschdFields.SchNum)
                            .InnerJoin(qf.Cell).On(CellFields.CellName == StationFields.Id)
                            .InnerJoin(qf.Sroute).On((SrouteFields.SchNum == SchdShipFields.SchNum).And(SrouteFields.Pass == OschdFields.Pass).And(SrouteFields.DroNum == StationFields.DroNum))
                            .LeftJoin(qf.Product).On(SchdShipFields.PartNum == ProductFields.ExternalId)
                            .LeftJoin(qf.Operation).On(SrouteFields.DroNum == OperationFields.ExternalId)
                            .LeftJoin(qf.ProductionPlan).On((ProductionPlanFields.WorkOrder == SchdShipFields.JobNo).And(ProductionPlanFields.OperationId == OperationFields.OperationId).And(ProductionPlanFields.OperationPass == OschdFields.Pass))
                            )
                           //.Where(ProductionPlanFields.ProductionPlanId.IsNotNull())
                           .OrderBy(SchdShipFields.JobNo.Ascending(), StationFields.DroNum.Ascending(), OschdFields.Pass.Ascending());

                    jobs = adapter.FetchQuery(query);

                }
            }
            catch (Exception e)
            {
                jobs = null;
                FileLog.Error("getScheduledJobs() - ", e);
            }

            return jobs;

        }

        static private string truncate(string txt, int max_len)
        {
            return (txt == null || txt.Length < max_len) ? txt : txt.Substring(0, max_len);
        }

        static private bool syncRequired()
        {
            bool required = false;
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    var qf = new QueryFactory();
                    var qry = qf.FileTime.OrderBy(FileTimeFields.FileTime.Descending());
                    var fte = adapter.FetchFirst(qry);
                    if (fte == null || fte.FileName != "VisiSuite")
                    {
                        required = true;
                    }
                }
                catch (Exception ex)
                {
                    required = false;
                    FileLog.Error("", ex);
                }
            }
            return required;
        }

        static private bool isSameJob(ProfitFabJob job1, ProfitFabJob job2)
        {
            return (job1 != null && job2 != null && job1.WorkOrder == job2.WorkOrder && job1.DronId == job2.DronId && job1.Pass == job2.Pass);
        }

        static public void syncProfitFabJobs()
        {

            // Sync jobs in ProfitFab with ProductionPlan / Product tables
            // The corresponding ProductionPlan entry has the WorkOrder = JobId-DronId-Pass

            if (!syncRequired())
                return;

            var profitfab_jobs = getScheduledJobs();

            if (profitfab_jobs == null) // we will use manually configured jobs or the last known good set of jobs
                return;

            var product_lookup = new Dictionary<string, ProductEntity>();
            var product_operation_time_lookup = new Dictionary<string, ProductOperationTimeEntity>();
            var production_plans_to_save = new EntityCollection<ProductionPlanEntity>();
            var product_operation_time_to_save = new EntityCollection<ProductOperationTimeEntity>();


            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    adapter.StartTransaction(IsolationLevel.ReadCommitted, "syncProfitFabJobs");

                    var qf = new QueryFactory();

                    ProductionPlanEntity ppe;
                    ProductEntity pe;
                    int idx = 0;
                    string key;
                    ProfitFabJob prev_job = null;

                    foreach (ProfitFabJob job in profitfab_jobs)
                    {

                        if (isSameJob(prev_job, job))
                            continue;

                        if (job.ProductionPlanId.HasValue && job.ProductionPlanId.Value > 0)
                        {
                            ppe = new ProductionPlanEntity(job.ProductionPlanId.Value);
                            if (!adapter.FetchEntity(ppe))
                                ppe = new ProductionPlanEntity();
                        }
                        else
                        {
                            ppe = new ProductionPlanEntity();
                        }

                        job.ProductionPlan = ppe;

                        ppe.ExternalId = job.ProductionPlanExternalId;
                        ppe.SortOrder = job.SortOrder;
                        ppe.DueDate = job.Start;
                        ppe.Planned = job.Quantity;
                        ppe.Closed = false;
                        ppe.Enabled = true;
                        ppe.CellId = job.CellId;
                        ppe.OperationId = job.OperationId;
                        ppe.OperationPass = (short)job.Pass;
                        ppe.StationId = job.StationNum;

                        if (ppe.IsNew)
                        {
                            ppe.WorkOrder = job.WorkOrder;
                            ppe.Actual = 0;
                            ppe.ActualDate = null;
                            ppe.CreatedDate = DateTime.Now;
                            ppe.CreatedBy = 1;
                            ppe.LastUpdatedDate = DateTime.Now;
                            ppe.LastUpdatedBy = 1;
                            ppe.SortOrder = idx;
                        }
                        else if (ppe.IsDirty)
                        {
                            ppe.LastUpdatedDate = DateTime.Now;
                            ppe.LastUpdatedBy = 1;
                        }

                        production_plans_to_save.Add(ppe);

                        // Product
                        key = job.ProductKey;

                        if (!product_lookup.TryGetValue(key, out pe))
                        {
                            if (job.ProductId.HasValue && job.ProductId.Value > 0)
                            {
                                pe = new ProductEntity(job.ProductId.Value);
                                if (!adapter.FetchEntity(pe))
                                    pe = new ProductEntity();
                            }
                            else
                            {
                                pe = new ProductEntity();
                            }

                            product_lookup.Add(key, pe);
                        }

                        pe.ExternalId = job.ProductExternalId;
                        pe.Description = job.PartDescription;
                        pe.Enabled = true;

                        if (pe.IsNew)
                        {
                            pe.Sku = job.Sku;
                            pe.Revision = job.PartRevision;
                            pe.CreatedDate = DateTime.Now;
                            pe.CreatedBy = 1;
                            pe.LastUpdatedDate = DateTime.Now;
                            pe.LastUpdatedBy = 1;
                        }
                        else if (pe.IsDirty)
                        {
                            pe.LastUpdatedDate = DateTime.Now;
                            pe.LastUpdatedBy = 1;
                        }

                        ppe.Product = pe;

                        prev_job = job;

                    }
                    adapter.SaveEntityCollection(production_plans_to_save, true, true);

                    // Add ProductOperationTime values
                    ProductOperationTimeEntity pote;
                    prev_job = null;
                    foreach (ProfitFabJob job in profitfab_jobs)
                    {
                        if (isSameJob(prev_job, job))
                            continue;

                        key = job.ProductionPlan.ProductId.ToString() + "|" + job.ProductionPlan.OperationId + "|" + job.ProductionPlan.OperationPass;
                        if (!product_operation_time_lookup.TryGetValue(key, out pote))
                        {
                            var pot_qry = qf.ProductOperationTime
                            .Where(ProductOperationTimeFields.ProductId == job.ProductionPlan.ProductId)
                            .AndWhere(ProductOperationTimeFields.OperationId == job.ProductionPlan.OperationId)
                            .AndWhere(ProductOperationTimeFields.OperationPass == job.ProductionPlan.OperationPass);
                            pote = adapter.FetchFirst(pot_qry);
                            if (pote == null)
                            {
                                pote = new ProductOperationTimeEntity
                                {
                                    ProductId = job.ProductionPlan.ProductId,
                                    OperationId = job.ProductionPlan.OperationId,
                                    OperationPass = job.ProductionPlan.OperationPass
                                };
                            }
                            product_operation_time_lookup.Add(key, pote);
                            product_operation_time_to_save.Add(pote);
                        }

                        pote.Rate = job.Rate;
                        pote.SetupTime = Convert.ToInt32(job.SetupTime * 3600);
                        pote.PartTime = Convert.ToInt32(job.PartTime * 3600);
                        pote.CycleTime = Convert.ToInt32(job.CycleTime * 3600);
                        pote.LoadUnloadTime = Convert.ToInt32(job.LoadUnloadTime * 3600);

                        prev_job = job;
                    }
                    adapter.SaveEntityCollection(product_operation_time_to_save, true, false);

                    // Disable all other Products / ProductionPlans / ProductOperatTimes

                    var ids = new List<int>();
                    foreach (var item in production_plans_to_save)
                    {
                        ids.Add(item.ProductionPlanId);
                    }
                    var qry = qf.ProductionPlan.Where(ProductionPlanFields.Closed == false).AndWhere(ProductionPlanFields.Enabled == true).AndWhere(ProductionPlanFields.ProductionPlanId.NotIn(ids));
                    var production_plans = adapter.FetchQuery(qry);
                    foreach (ProductionPlanEntity pp in production_plans)
                    {
                        pp.Enabled = false;
                    }
                    adapter.SaveEntityCollection(production_plans);

                    ids.Clear();
                    foreach (var item in product_lookup)
                    {
                        ids.Add(item.Value.ProductId);
                    }
                    var qry2 = qf.Product.Where(ProductFields.Enabled == true).AndWhere(ProductFields.ProductId.NotIn(ids));
                    var products = adapter.FetchQuery(qry2);
                    foreach (ProductEntity pp in products)
                    {
                        pp.Enabled = false;
                    }
                    adapter.SaveEntityCollection(products);

                    ids.Clear();
                    foreach (var item in product_operation_time_lookup)
                    {
                        ids.Add(item.Value.ProductOperationTimeId);
                    }
                    var qry3 = qf.ProductOperationTime.Where(ProductOperationTimeFields.Enabled == true).AndWhere(ProductOperationTimeFields.ProductOperationTimeId.NotIn(ids));
                    var product_operation_times = adapter.FetchQuery(qry3);
                    foreach (ProductOperationTimeEntity pot in product_operation_times)
                    {
                        pot.Enabled = false;
                    }
                    adapter.SaveEntityCollection(product_operation_times);

                    adapter.Commit();

                    FileLog.Error("New data synced.");
                }
                catch (Exception ex)
                {
                    adapter.Rollback();
                    FileLog.Error("syncProfitFabJobs() - ", ex);
                }
            }

        }

        static public void syncProfitFabEmployees()
        {

            // Sync Employees in ProfitFab with Operator table

            var operator_lookup = new Dictionary<string, OperatorEntity>();
            var operators_to_save = new EntityCollection<OperatorEntity>();

            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    adapter.StartTransaction(IsolationLevel.ReadCommitted, "syncProfitFabEmployees");

                    var qf = new QueryFactory();

                    #region Operators from Employee
                    // http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

                    // Disable all those NOT in the ProfitFab Employee
                    var oe = new OperatorEntity();
                    oe.Active = false;
                    RelationPredicateBucket filter_bucket = new RelationPredicateBucket(OperatorFields.ExternalId.NotIn(qf.Create().Select(EmployeeFields.EmpNum)));
                    int cnt = adapter.UpdateEntitiesDirectly(oe, filter_bucket);

                    // Update those from the ProfitFab Employee table
                    var operator_update_qry = qf.Create()
                        .Select(() => new OperatorEntity()
                        {
                            OperatorId = OperatorFields.OperatorId.ToValue<int>(),
                            ExternalId = EmployeeFields.EmpNum.ToValue<int>(),
                            AreaId = _area_id,
                            EmployeeCode = EmployeeFields.Id.ToValue<string>(),
                            FirstName = EmployeeFields.Name.ToValue<string>(),
                            LastName = "",
                            Active = true,
                            IsNew = false
                        })
                        .From(qf.Employee
                              .InnerJoin(qf.Operator).On(EmployeeFields.EmpNum == OperatorFields.ExternalId)
                             );
                    var operators = adapter.FetchQuery(operator_update_qry);
                    adapter.SaveEntityCollection(new EntityCollection<OperatorEntity>(operators));

                    // Add those new ones from the ProfitFab Employee table
                    var operator_add_qry = qf.Create()
                        .Select(() => new OperatorEntity()
                        {
                            ExternalId = EmployeeFields.EmpNum.ToValue<int>(),
                            AreaId = _area_id,
                            EmployeeCode = EmployeeFields.Id.ToValue<string>(),
                            FirstName = EmployeeFields.Name.ToValue<string>(),
                            LastName = "",
                            Active = true,
                            IsNew = true
                        })
                        .From(qf.Employee
                              .LeftJoin(qf.Operator).On(EmployeeFields.EmpNum == OperatorFields.ExternalId)
                             )
                        .Where(OperatorFields.OperatorId.IsNull());
                    operators = adapter.FetchQuery(operator_add_qry);
                    adapter.SaveEntityCollection(new EntityCollection<OperatorEntity>(operators));

                    #endregion


                    #region OperatorAuth from EmpOper

                    truncateVisiSuiteTable(adapter, "OperatorAuth");
                    
                    // Items to add to OperAuth
                    var operator_auth_qry = qf.Create()
                        .Select(() => new OperatorAuthEntity()
                        {
                            OperatorId = OperatorFields.OperatorId.ToValue<int>(),
                            OperationId = OperationFields.OperationId.ToValue<int?>(),
                            IsNew = true
                        })
                        .From(qf.EmpOper
                            .InnerJoin(qf.Operator).On(OperatorFields.ExternalId == EmpOperFields.EmpNum)
                            .InnerJoin(qf.Operation).On(OperationFields.ExternalId == EmpOperFields.DroNum)
                            );
                    var operator_auths = adapter.FetchQuery(operator_auth_qry);
                    adapter.SaveEntityCollection(new EntityCollection<OperatorAuthEntity>(operator_auths));

                    #endregion

                    qf = null;
                    adapter.Commit();

                }
                catch (Exception ex)
                {
                    adapter.Rollback();
                    FileLog.Error("syncProfitFabEmployees() - ", ex);
                }
            }

        }

        static public void syncProfitFabOperations()
        {

            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    adapter.StartTransaction(IsolationLevel.ReadCommitted, "syncProfitFabOperations");

                    var qf = new QueryFactory();

                    // Populate Operations from DRoute
                    // http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

                    // Items to mark as disabled in Operation table
                    var disabled_qry = qf.Operation
                        .Where(OperationFields.ExternalId.NotIn(qf.Create().Select(DrouteFields.DroNum)));
                    var disabled_list = adapter.FetchQuery(disabled_qry);
                    foreach (OperationEntity item in disabled_list)
                    {
                        item.Enabled = false;
                    }
                    adapter.SaveEntityCollection(disabled_list);

                    // Items to add to Operation table

                    var add_qry = qf.Create()
                        .Select(() => new OperationEntity()
                        {
                            Description = DrouteFields.Description.ToValue<string>(),
                            ExternalId = DrouteFields.DroNum.ToValue<int>(),
                            Enabled = true
                        })
                        .From(qf.Droute
                            .LeftJoin(qf.Operation).On(OperationFields.ExternalId == DrouteFields.DroNum)
                            )
                            .Where(OperationFields.OperationId.IsNull());
                    var add_list = adapter.FetchQuery(add_qry);
                    var add_collection = new EntityCollection<OperationEntity>(add_list);
                    adapter.SaveEntityCollection(add_collection);

                    adapter.Commit();

                }
                catch (Exception ex)
                {
                    adapter.Rollback();
                    FileLog.Error("syncProfitFabOperations() - ", ex);
                }
            }

        }
        
    }
}
